package mytest.lock;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import mytest.util.ConnectionUtil;

public class LockDao {

	/**
	 * lock_id没有添加索引锁全表，
	 * 添加索引之后变成gap锁，多线程情况下造成死锁。
	 * 解决方案：设置事务隔离级别为Read committed（RC）提交读
	 */
	public void save() {
		Connection conn = null;
		try {
			//线程问题？把Connection声明到try外面就没有deadlock了
			conn = ConnectionUtil.getCon();
			conn.setAutoCommit(false);
			int lockId = (int)(Math.random()*1000);
			String name = "name-" + lockId;
			PreparedStatement ps = conn.prepareStatement("insert into t_lock(lock_id, name, create_time) values(?, ?, now())");
			ps.setInt(1, lockId);
			ps.setString(2, name);
			ps.execute();
			
			PreparedStatement updateps = conn.prepareStatement("update t_lock set create_time=now() where lock_id=?");
			updateps.setInt(1, lockId);
			updateps.execute();
			
			Thread.sleep(1000);
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}
}
